package cn.goour.web.dser.service

import cn.goour.utils.io.IO
import cn.goour.web.base.entity.PageInfo
import cn.goour.web.dser.entity.SchoolIp
import cn.goour.web.dser.repository.SchoolIpRepository
import cn.goour.web.sys.entity.Admin
import cn.goour.web.sys.entity.Log
import cn.goour.web.sys.entity.type.ActionType
import cn.goour.web.sys.repository.LogRepository
import getAdmin
import getUser
import org.apache.poi.hssf.usermodel.HSSFWorkbook
import org.apache.poi.poifs.filesystem.POIFSFileSystem
import org.apache.poi.ss.usermodel.Cell
import org.apache.poi.ss.usermodel.CellType
import org.apache.poi.xssf.usermodel.XSSFWorkbook
import org.slf4j.LoggerFactory
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.data.domain.Page
import org.springframework.data.domain.PageRequest
import org.springframework.data.domain.Sort
import org.springframework.data.jpa.domain.Specification
import org.springframework.scheduling.annotation.Async
import org.springframework.stereotype.Service
import org.springframework.web.context.request.RequestContextHolder
import org.springframework.web.context.request.ServletRequestAttributes
import java.io.File
import java.io.IOException
import java.text.DecimalFormat
import java.text.NumberFormat
import java.util.*
import javax.persistence.criteria.CriteriaBuilder
import javax.persistence.criteria.CriteriaQuery
import javax.persistence.criteria.Root
import javax.transaction.Transactional


@Service
@Transactional
open class SchoolIpService {
    @Autowired private
    lateinit var repository: SchoolIpRepository
    @Autowired private
    lateinit var logRepository: LogRepository

    private val df: DecimalFormat = NumberFormat.getInstance() as DecimalFormat

    init {
        df.maximumFractionDigits = 8
    }

    @Throws(Exception::class)
    open fun findAll(): MutableList<SchoolIp>? = repository.findAll()

    open fun getList(pageInfo: PageInfo): Page<SchoolIp>? {

        val pageRequest = PageRequest(pageInfo.page - 1, pageInfo.size, Sort(Sort.Order(Sort.Direction.ASC, "building"), Sort.Order(Sort.Direction.ASC, "room")))
        return repository.findAll(Specification { root: Root<SchoolIp>, _: CriteriaQuery<*>, criteriaBuilder: CriteriaBuilder ->
            val search = pageInfo.search
            val re = getIpForSearchStr(search)
            if (re == null) {
                val c1 = criteriaBuilder.like(root.get("ip1"), "%$search%")
                val c2 = criteriaBuilder.like(root.get("ip2"), "%$search%")

                val c3 = criteriaBuilder.like(root.get("dns1"), "%$search%")
                val c4 = criteriaBuilder.like(root.get("dns2"), "%$search%")

                val c5 = criteriaBuilder.like(root.get("mask"), "%$search%")
                val c6 = criteriaBuilder.like(root.get("gw"), "%$search%")

                val c7 = criteriaBuilder.like(root.get("room"), "%$search%")
                val c8 = criteriaBuilder.like(root.get("building"), "%$search%")

                criteriaBuilder.or(c1, c2, c3, c4, c5, c6, c7, c8)
            } else {
                val c1 = criteriaBuilder.equal(root.get<String>("building"), re.building)
                val c2 = criteriaBuilder.equal(root.get<String>("room"), re.room)

                criteriaBuilder.and(c1, c2)
            }
        }, pageRequest)
    }

    open fun getByBuildingAndRoom(building: String, room: String): SchoolIp? {
        val request = (RequestContextHolder.getRequestAttributes() as ServletRequestAttributes).request

        val re = repository.getByBuildingAndRoom(building, room)
        val success = if (re != null) {
            "获取到数据"
        } else {
            "没有数据"
        }
        val log = Log(user = request.getUser(), actionType = ActionType.SchoolIpSearch, actionText = "${ActionType.SchoolIpSearch.text}：$building - ${room}：$success", request = request)
        logRepository.save(log)

        return re
    }

    open fun getSchoolIp(search: String): SchoolIp? {
        var re = getIpForSearchStr(search)
        if (re != null) {
            re = repository.getByBuildingAndRoom(re.building, re.room)
        }
        return re
    }

    private fun getIpForSearchStr(search: String): SchoolIp? {
        val match = "^([0-9]{1,2})([0-9]{3})$|^([0-9]{1,2})栋([0-9]{3})$".toRegex().matchEntire(search.trim())
        var ip: SchoolIp? = null
        if (match != null) {
            val values = match.groupValues
            ip = if (!values[1].isBlank()) {
                SchoolIp(building = values[1], room = values[2])
            } else {
                SchoolIp(building = values[3], room = values[4])
            }
        }
        return ip
    }

    open fun add(schoolIp: SchoolIp): SchoolIp {
        val request = (RequestContextHolder.getRequestAttributes() as ServletRequestAttributes).request

        val log = Log(admin = request.getAdmin(), actionType = ActionType.SchoolIpAdd, actionText = "${ActionType.SchoolIpAdd.text}：${schoolIp.building}${schoolIp.room}", request = request)
        logRepository.save(log)

        return repository.save(schoolIp)
    }

    open fun update(schoolIp: SchoolIp): SchoolIp {
        val request = (RequestContextHolder.getRequestAttributes() as ServletRequestAttributes).request

        val log = Log(admin = request.getAdmin(), actionType = ActionType.SchoolIpUpdate, actionText = "${ActionType.SchoolIpUpdate.text}：${schoolIp.building}${schoolIp.room}", request = request)
        logRepository.save(log)

        return repository.saveAndFlush(schoolIp)
    }

    open fun delete(id: Array<Int>) {
        val request = (RequestContextHolder.getRequestAttributes() as ServletRequestAttributes).request
        val my = request.getAdmin()
        id.forEach {
            val vo = repository.getOne(it)
            val log = Log(admin = my, actionType = ActionType.SchoolIpDel, actionText = "${ActionType.SchoolIpDel.text}：${vo.building}${vo.room}", request = request)
            logRepository.save(log)
            repository.delete(vo)
        }
        logRepository.flush()
        repository.flush()
    }


    open fun clear() {
        try {
            repository.clearTable()
        } catch (e: Exception) {
            e.printStackTrace()
            logger.error("使用原生sql清空IP数据表错误", e)
            repository.deleteAll()
        }
    }


    @Async
    open fun saveAll(ext: String, file: File, admin: Admin, ip: String, ua: String) {
        try {
            var log = Log(admin = admin, actionType = ActionType.SchoolIpAdd, actionText = "导入IP数据准备中，正在解析数据文件", addIp = ip, addUa = ua)
            logRepository.save(log)

            val list = ArrayList<SchoolIp>()
            when (ext) {
                "xls" -> list.addAll(parseXLS(file))
                "xlsx" -> list.addAll(parseXLSX(file))
                "csv" -> list.addAll(parseCSV(file))
            }
            log = Log(admin = admin, actionType = ActionType.SchoolIpAdd, actionText = "导入IP数据准备中，数据文件解析完毕", addIp = ip, addUa = ua)
            logRepository.save(log)
            saveAll(list, admin, ip, ua)
            logger.info("导入条数：${list.size}")
        } catch (e: Exception) {
            logger.error("导入IP失败，解析文件错误", e)
            val log = Log(admin = admin, actionType = ActionType.SchoolIpAdd, actionText = "导入IP数据失败，数据文件解析错误", addIp = ip, addUa = ua)
            logRepository.save(log)
        }
    }

    @Async
    open fun saveAll(list: ArrayList<SchoolIp>, admin: Admin, ip: String, ua: String) {

        var log = Log(admin = admin, actionType = ActionType.SchoolIpAdd, actionText = "正在批量导入IP数据，共${list.size}条", addIp = ip, addUa = ua)
        logRepository.save(log)
        var errorNum = 0
        list.forEach {
            try {
                repository.save(it)
            } catch (e: Exception) {
                ++errorNum
            }
        }

        log = Log(admin = admin, actionType = ActionType.SchoolIpAdd, actionText = "批量导入IP数据完毕，共${list.size}条，导入错误${errorNum}条", addIp = ip, addUa = ua)
        logRepository.save(log)
    }

    @Throws(Exception::class)
    private fun parseXLS(file: File): List<SchoolIp> {
        val list = ArrayList<SchoolIp>()
        try {
            val ts = POIFSFileSystem(file)
            val workbook = HSSFWorkbook(ts)
            val sh = workbook.getSheetAt(0)
            sh.forEachIndexed { index, row ->
                if (index >= 1 && row.lastCellNum >= 8) {
                    val ip = SchoolIp(
                            building = parseCellToString(row.getCell(0)),
                            room = parseCellToString(row.getCell(1)),
                            ip1 = parseCellToString(row.getCell(2)),
                            ip2 = parseCellToString(row.getCell(3)),
                            gw = parseCellToString(row.getCell(4)),
                            mask = parseCellToString(row.getCell(5)),
                            dns1 = parseCellToString(row.getCell(6)),
                            dns2 = parseCellToString(row.getCell(7))
                    )
                    list.add(ip)
                }
            }
            workbook.close()
        } catch (e: Exception) {
            e.printStackTrace()
            throw e
        }

        return list
    }

    @Throws(Exception::class)
    private fun parseXLSX(file: File): List<SchoolIp> {
        val list = ArrayList<SchoolIp>()
        try {
            val workbook = XSSFWorkbook(file)
            val sh = workbook.getSheetAt(0)
            sh.forEachIndexed { index, row ->
                if (index >= 1 && row.lastCellNum >= 8) {
                    val ip = SchoolIp(
                            building = parseCellToString(row.getCell(0)),
                            room = parseCellToString(row.getCell(1)),
                            ip1 = parseCellToString(row.getCell(2)),
                            ip2 = parseCellToString(row.getCell(3)),
                            gw = parseCellToString(row.getCell(4)),
                            mask = parseCellToString(row.getCell(5)),
                            dns1 = parseCellToString(row.getCell(6)),
                            dns2 = parseCellToString(row.getCell(7))
                    )
                    list.add(ip)
                }
            }
            workbook.close()
        } catch (e: Exception) {
            e.printStackTrace()
            throw e
        }

        return list
    }

    private fun parseCellToString(cell: Cell?): String {
        var re = ""
        if (cell == null) {
            return re
        }
        when (cell.cellTypeEnum) {
            CellType._NONE -> {
            }
            CellType.NUMERIC -> re = df.format(cell.numericCellValue)
            CellType.STRING -> re = cell.stringCellValue
            CellType.FORMULA -> re = cell.cellFormula
            CellType.BLANK -> {
            }
            CellType.BOOLEAN -> {
            }
            CellType.ERROR -> {
            }
            else -> {
            }
        }
        return re
    }

    @Throws(Exception::class)
    private fun parseCSV(file: File): List<SchoolIp> {
        val list = ArrayList<SchoolIp>()
        try {
            /*
			 * Map<String, Integer> colIndex = new HashMap<>(); String[] field =
			 * new String[] { "building", "room", "ip1", "ip2", "gw", "mask",
			 * "dns1", "dns2" };
			 */
            val input = file.inputStream()
            val content = String(IO.read(input))
            content.replace("\r".toRegex(), "")
            val row = content.split("\n".toRegex()).dropLastWhile({ it.isEmpty() }).toTypedArray()
            for (i in 1 until row.size) {
                val col = row[i].split(",".toRegex()).dropLastWhile({ it.isEmpty() }).toTypedArray()
                if (i > 0 && col.size >= 8) {
                    val ip = SchoolIp(building = col[0], room = col[1], ip1 = col[2], ip2 = col[3], gw = col[4], mask = col[5], dns1 = col[6], dns2 = col[7])
                    list.add(ip)
                }
            }
            input.close()
        } catch (e: IOException) {
            e.printStackTrace()
            throw e
        }

        return list
    }

    companion object {
        private val logger = LoggerFactory.getLogger(SchoolIpService::class.java)

    }
}